package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblLegalNegotiatedsettlemenMySql;

import java.math.BigDecimal;

public class TblLegalNegotiatedsettlemenMySqlMapperSqlConfig {

    public String findListByPage(PageInfo<TblLegalNegotiatedsettlemenMySql> pageInfo, TblLegalNegotiatedsettlemenMySql negotia, BigDecimal pid,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN tln\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tln.DISPUINFO = tld.DISPUTEID\n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu on tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE tln.LINKORG ="+pid);

        if (disputeid != null){
            sqlSb.append("AND tld.DISPUTEID = "+disputeid);
        }
        if (negotia.getDisputeitem() != null && !"".equals(negotia.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + negotia.getDisputeitem() + "%'");
        }
        if (negotia.getDisputetype() != null && !"".equals(negotia.getDisputetype())) {
            sqlSb.append(" AND tld.DISPUTETYPE LIKE '%" + negotia.getDisputetype() + "%'");
        }
        if (negotia.getContractname() != null && !"".equals(negotia.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + negotia.getContractname() + "%'");
        }
        if (negotia.getCounterpart() != null) {
            sqlSb.append(" AND COUNTERPART like '%" + negotia.getCounterpart() + "%' ");
        }

        sqlSb.append(" ORDER BY negotiaId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageCount(TblLegalNegotiatedsettlemenMySql negotia, BigDecimal pid,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT count(*) FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN tln\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tln.DISPUINFO = tld.DISPUTEID\n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu on tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE tln.LINKORG ="+pid);

        if (disputeid != null){
            sqlSb.append("AND tld.DISPUTEID = "+disputeid);
        }
        if (negotia.getDisputeitem() != null && !"".equals(negotia.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + negotia.getDisputeitem() + "%'");
        }
        if (negotia.getDisputetype() != null && !"".equals(negotia.getDisputetype())) {
            sqlSb.append(" AND tld.DISPUTETYPE LIKE '%" + negotia.getDisputetype() + "%'");
        }
        if (negotia.getContractname() != null && !"".equals(negotia.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + negotia.getContractname() + "%'");
        }
        if (negotia.getCounterpart() != null) {
            sqlSb.append(" AND COUNTERPART like '%" + negotia.getCounterpart() + "%' ");
        }

        sqlSb.append(" ORDER BY negotiaId DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String saveDiputregistration(TblLegalNegotiatedsettlemenMySql negotiated) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_LEGAL_NEGOTIATEDSETTLEMEN (NEGOTIAID");
        StringBuffer value = new StringBuffer(" VALUES (CIRCULATION_SEQUENCE.nextval");

        if(negotiated.getNegotiastatus() != null && !"".equals(negotiated.getNegotiastatus())) {
            column.append(",NEGOTIASTATUS");
            value.append(",'"+negotiated.getNegotiastatus()+"'");
        }
        if(negotiated.getCounterpart() != null && !"".equals(negotiated.getCounterpart())) {
            column.append(",COUNTERPART");
            value.append(",'"+negotiated.getCounterpart()+"'");
        }
        if(negotiated.getCounterpartphone() != null && !"".equals(negotiated.getCounterpartphone())) {
            column.append(",COUNTERPARTPHONE");
            value.append(",'"+negotiated.getCounterpartphone()+"'");
        }
        if(negotiated.getIsaggree() != null && !"".equals(negotiated.getIsaggree())) {
            column.append(",ISAGGREE");
            value.append(",'"+negotiated.getIsaggree()+"'");
        }
        if(negotiated.getNegetiaresult() != null && !"".equals(negotiated.getNegetiaresult())) {
            column.append(",NEGETIARESULT");
            value.append(",'"+negotiated.getNegetiaresult()+"'");
        }
        if(negotiated.getJudicialsettlement() != null && !"".equals(negotiated.getJudicialsettlement())) {
            column.append(",JUDICIALSETTLEMENT");
            value.append(",'"+negotiated.getJudicialsettlement()+"'");
        }
        if(negotiated.getLinkorg() != null && !"".equals(negotiated.getLinkorg())) {
            column.append(",LINKORG");
            value.append(",'"+negotiated.getLinkorg()+"'");
        }
        if(negotiated.getDispuinfo() != null && !"".equals(negotiated.getDispuinfo())) {
            column.append(",DISPUINFO");
            value.append(",'"+negotiated.getDispuinfo()+"'");
        }
        if(negotiated.getCreatestaff() != null && !"".equals(negotiated.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+negotiated.getCreatestaff()+"'");
        }
        if(negotiated.getCreatetime() != null && !"".equals(negotiated.getCreatetime())) {
            column.append(",CREATETIME");
            value.append(",TO_DATE('"+ DateUtil.parseDate(negotiated.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //value.append(",'"+negotiated.getCreatetime()+"'");
        }
        if(negotiated.getSolutionmode() != null && !"".equals(negotiated.getSolutionmode())) {
            column.append(",SOLUTIONMODE");
            value.append(",'"+negotiated.getSolutionmode()+"'");
        }
        if(negotiated.getCourtname() != null && !"".equals(negotiated.getCourtname())) {
            column.append(",COURTNAME");
            value.append(",'"+negotiated.getCourtname()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String updateOldNegotiated(TblLegalNegotiatedsettlemenMySql oldNegotiated) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_LEGAL_NEGOTIATEDSETTLEMEN SET COUNTERPART = '"+oldNegotiated.getCounterpart()+"' ");

        if(oldNegotiated.getNegotiastatus() != null && !"".equals(oldNegotiated.getNegotiastatus())) {
            sql.append(" , NEGOTIASTATUS = '"+oldNegotiated.getNegotiastatus()+"'");
        }
        if(oldNegotiated.getCounterpartphone() != null && !"".equals(oldNegotiated.getCounterpartphone())) {
            sql.append(" , COUNTERPARTPHONE = '"+oldNegotiated.getCounterpartphone()+"'");
        }
        if(oldNegotiated.getIsaggree() != null && !"".equals(oldNegotiated.getIsaggree())) {
            sql.append(" , ISAGGREE = '"+oldNegotiated.getIsaggree()+"'");
        }
        if(oldNegotiated.getNegetiaresult() != null && !"".equals(oldNegotiated.getNegetiaresult())) {
            sql.append(" , NEGETIARESULT = '"+oldNegotiated.getNegetiaresult()+"'");
        }
        if(oldNegotiated.getJudicialsettlement() != null && !"".equals(oldNegotiated.getJudicialsettlement())) {
            sql.append(" , JUDICIALSETTLEMENT = '"+oldNegotiated.getJudicialsettlement()+"'");
        }
        if(oldNegotiated.getLinkorg() != null && !"".equals(oldNegotiated.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldNegotiated.getLinkorg()+"'");
        }
        if(oldNegotiated.getDispuinfo() != null && !"".equals(oldNegotiated.getDispuinfo())) {
            sql.append(" , DISPUINFO = '"+oldNegotiated.getDispuinfo()+"'");
        }
        if(oldNegotiated.getCreatestaff() != null && !"".equals(oldNegotiated.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldNegotiated.getCreatestaff()+"'");
        }
        if(oldNegotiated.getCreatetime() != null && !"".equals(oldNegotiated.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(oldNegotiated.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //sql.append(" , CREATETIME = '"+oldNegotiated.getCreatetime()+"'");
        }
        if(oldNegotiated.getSolutionmode() != null && !"".equals(oldNegotiated.getSolutionmode())) {
            sql.append(" , SOLUTIONMODE = '"+oldNegotiated.getSolutionmode()+"'");
        }
        if(oldNegotiated.getCourtname() != null && !"".equals(oldNegotiated.getCourtname())) {
            sql.append(" , COURTNAME = '"+oldNegotiated.getCourtname()+"'");
        }

        sql.append(" WHERE NEGOTIAID = '"+oldNegotiated.getNegotiaid()+"'");
        return sql.toString();
    }


    public String findListForLitiationList(PageInfo<TblLegalNegotiatedsettlemenMySql> pageInfo, TblLegalNegotiatedsettlemenMySql negotiate) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN tln\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld ON tln.DISPUINFO = tld.DISPUTEID \n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu ON tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE tln.LINKORG = "+ negotiate.getLinkorg());

        if (negotiate.getJudicialsettlement() != null) {
            sqlSb.append(" AND JUDICIALSETTLEMENT = " + negotiate.getJudicialsettlement());
        }
        sqlSb.append(" AND tld.DISPUTEID NOT IN (SELECT tld.DISPUTEID FROM TBL_LEGAL_CLOSEINFORMATION tlc\n" +
                " LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tlc.DISPUTINFO = tld.DISPUTEID\n" +
                " WHERE tld.LINKORG = "+ negotiate.getLinkorg());

        sqlSb.append(" )ORDER BY negotiaId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListForLitiationListCount(TblLegalNegotiatedsettlemenMySql negotiate) throws Exception {
        StringBuffer sqlSb = new StringBuffer(" SELECT COUNT(*) FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN tln\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld ON tln.DISPUINFO = tld.DISPUTEID \n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu ON tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE tln.LINKORG = "+ negotiate.getLinkorg());

        if (negotiate.getJudicialsettlement() != null) {
            sqlSb.append(" AND JUDICIALSETTLEMENT = " + negotiate.getJudicialsettlement());
        }
        sqlSb.append(" AND tld.DISPUTEID NOT IN (SELECT tld.DISPUTEID FROM TBL_LEGAL_CLOSEINFORMATION tlc\n" +
                " LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tlc.DISPUTINFO = tld.DISPUTEID\n" +
                " WHERE tld.LINKORG = "+ negotiate.getLinkorg());

        sqlSb.append(" )ORDER BY negotiaId DESC");
        String sql = sqlSb.toString();
        return sql;
    }
}
